<!--- Read data from two datasource tables. ---> 


<cfquery 
       name="conds" datasource="exp" >

       SELECT distinct conditions.description, trial.condid
       FROM trial, experiment, conditions, stimuli
       where  trial.condid=conditions.id
       and trial.stimid=stimuli.id
       and trial.expid = experiment.id
       and experiment.id = #expid#
       
</cfquery> 
<cfscript> 
//Use an absolute path for the files. ---> 
theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
theFile=theDir & "experiment.xls"; 
</cfscript>



<cfset flag = true>
<cfloop query = "conds">

<cfquery 
       name="getcons" datasource="exp" >
       SELECT distinct trial.id, experiment.title, conditions.description,location, question, istext, isaudio, isvideo, ispicture, answer 
       FROM trial, experiment, conditions, stimuli
       where conditions.id=#condid#
       and trial.condid=conditions.id
       and trial.stimid=stimuli.id
       and trial.expid = experiment.id
       and experiment.id = #expid#
</cfquery> 
     
<cfscript> 
    //Create an empty ColdFusion spreadsheet objects. ---> 
    theSheet = SpreadsheetNew(#description# ); 
    //Populate each object with a query. ---> 
    SpreadsheetAddRow(theSheet,"trial id, title, description, location, question,  istext, isaudio, isvideo, ispicture, answer");
    SpreadsheetAddRows(theSheet,getcons); 
    

</cfscript> 
 
<!--- Write the two sheets to a single file ---> 



<cfif flag>
<cfspreadsheet action="write" filename="#theFile#" name="theSheet"  
    sheetname="#description#" overwrite="true"> 
    
    <cfset flag = false>
<cfelse>
<cfspreadsheet action="update" filename="#theFile#" name="theSheet"  
    sheetname="#description#"> 
</cfif>    
    

</cfloop>

<cfloop query = "conds">

<cfquery 
       name="getparts" datasource="exp" >
    	select participantid, conditions.description as conddescript, stimuli.location, givesanswer.answer
    	from givesanswer, trial, stimuli, conditions
    	where conditions.id=#condid# 
    	and trial.stimid = stimuli.id
    	and conditions.id = trial.condid
    	and givesanswer.trialid = trial.id
    	order by participantid
       
</cfquery> 
     
<cfscript> 
    //Create an empty ColdFusion spreadsheet objects. ---> 
    theSheet = SpreadsheetNew("#getparts.conddescript#participant"); 
    //Populate each object with a query. ---> 
    SpreadsheetAddRow(theSheet,"Participant id, condition, description, answer");
    SpreadsheetAddRows(theSheet,getparts); 
</cfscript> 
 
<!--- Write the two sheets to a single file ---> 


<cfspreadsheet action="update" filename="#theFile#" name="theSheet"  
    sheetname="#getparts.conddescript#participant"> 
  
    

</cfloop>



<cfquery name="getemail" datasource="exp" >
select * from user where id=#session.id#
</cfquery>


<cfmail type="html" 
from="#getemail.email#" 
subject="Experiment XLS" 
to="#getemail.email#"
server="mr1.dcs.gla.ac.uk"
spoolenable="no">

<cfmailparam file="#theFile#">

Attached find the XLS file
</cfmail> 

<cfset session.message = "Your XLS file has been emailed to you">
<cflocation url="/ecada/analyse/seeexp.cfm?id=#expid#">

